import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelDataImport {
	private final String SCHEMA = "flg";
	private final String EXT = ".xlsx";

	private final String IMG_HEAD = "http://image.yiqifa.com/img";

	private final SimpleDateFormat FORMATTER = new SimpleDateFormat("yyyy-MM-dd");

	private void run(String dirName) {
		Map<Integer, String> brands = new HashMap<>();
		Map<Integer, String> categories = new HashMap<>();

		File dir = new File(dirName);
		File[] files = dir.listFiles((originalDir, name) -> name.endsWith(EXT));

		Map<String, List<OriginalProduct>> allProducts = new HashMap<>();
		if (files != null) {
			for (File file : files) {
				String fullName = file.getName();
				String name = cutFileName(fullName);
				List<OriginalProduct> products = read(file.getAbsolutePath());

				allProducts.put(name, products);
			}
		}

		List<String> commoditySql = new ArrayList<>();

		for (Map.Entry<String, List<OriginalProduct>> entry : allProducts.entrySet()) {
			String brandIdName = entry.getKey();
			List<OriginalProduct> products = entry.getValue();

			int brandId = 0;
			for (Map.Entry<Integer, String> brand : brands.entrySet()) {
				int id = brand.getKey();
				String idName = brand.getValue();

				if (brandIdName.equals(idName)) {
					brandId = id;
					break;
				}
			}

			if (brandId == 0) {
				int newId = Integer.valueOf(cutBrandId(brandIdName));
				brands.put(newId, cutBrandName(brandIdName));
				brandId = newId;
			}

			SpreadUrl spreadUrl = new SpreadUrl(brandId);

			for (OriginalProduct product : products) {
				String sql = "INSERT INTO " + SCHEMA + ".tg_commodity (";
				sql += "suppliers_id, brand_id, category1, store_name, suppliers_commodity_id, commodity_name, ";
				sql += "commodity_url, commodity_img_url, commodity_price, commission, commission_rate, rebate_rate, ";
				sql += "sale_state, sales, promotion_start_time, promotion_end_time, ";
				sql += "del_flg, add_user, add_time, upd_user, upd_time) VALUES (";

				sql += "1, ";//suppliers_id
				sql += brandId + ", ";//brand_id

				int categoryId = 0;
				for (Map.Entry<Integer, String> category : categories.entrySet()) {
					int id = category.getKey();
					String name = category.getValue();

					if (product.getCategory().equals(name)) {
						categoryId = id;
						break;
					}
				}

				if (categoryId == 0) {
					int newId = categories.size() + 1;
					categories.put(newId, product.getCategory());
					categoryId = newId;
				}

				sql += categoryId + ", ";//category1
				sql += "'" + escape(product.getShopName()) + "', ";//store_name
				sql += "'" + escape(product.getId()) + "', ";//suppliers_commodity_id
				sql += "'" + escape(product.getName()) + "', ";//commodity_name
				sql += "'" + escape(spreadUrl.generateUrl(product.getUrl())) + "', ";//commodity_url
				sql += "'" + escape(addImageHeadUrl(product.getImgUrl())) + "', ";//commodity_img_url
				sql += product.getPrice() + ", ";//commodity_price
				sql += product.getCommission() * 100 + ", ";//commission
				sql += (product.getCommissionRate() / 100) + ", ";//commission_rate
				sql += "0.4, ";//rebate_rate
				sql += "1, ";//sale_state
				sql += product.getHistorySales() + ", ";//sales
				sql += "'" + FORMATTER.format(product.getStartDate()) + "', ";//promotion_start_time
				sql += "'" + FORMATTER.format(product.getEndDate()) + "', ";//promotion_end_time
				sql += "'0', ";//del_flg
				sql += "'admin', ";//add_user
				sql += "CURRENT_TIMESTAMP, ";//add_time
				sql += "'admin', ";//upd_user
				sql += "CURRENT_TIMESTAMP); ";//upd_time

				commoditySql.add(sql);
			}
		}

		List<String> brandSql = new ArrayList<>();
		for (Map.Entry<Integer, String> brand : brands.entrySet()) {
			int id = brand.getKey();
			String name = brand.getValue();

			String sql = "INSERT INTO " + SCHEMA + ".tg_brand (";
			sql += "brand_id, brand_name, url_type, ";
			sql += "del_flg, add_user, add_time, upd_user, upd_time) VALUES (";

			sql += id + ", ";//brand_id
			sql += "'" + escape(name) + "', ";//brand_name
			sql += "1";//url_type
			sql += "'0', ";//del_flg
			sql += "'admin', ";//add_user
			sql += "CURRENT_TIMESTAMP, ";//add_time
			sql += "'admin', ";//upd_user
			sql += "CURRENT_TIMESTAMP); ";//upd_time

			brandSql.add(sql);
		}

		List<String> categorySql = new ArrayList<>();
		for (Map.Entry<Integer, String> category : categories.entrySet()) {
			int id = category.getKey();
			String name = category.getValue();

			String sql = "INSERT INTO " + SCHEMA + ".tg_classification_first (";
			sql += "classification_first_id, classification_first_name, ";
			sql += "del_flg, add_user, add_time, upd_user, upd_time) VALUES (";

			sql += id + ", ";//brand_id
			sql += "'" + escape(name) + "', ";//brand_name
			sql += "'0', ";//del_flg
			sql += "'admin', ";//add_user
			sql += "CURRENT_TIMESTAMP, ";//add_time
			sql += "'admin', ";//upd_user
			sql += "CURRENT_TIMESTAMP); ";//upd_time

			categorySql.add(sql);
		}

		FileWriter commodityWriter = null;
		FileWriter brandWriter = null;
		FileWriter categoryWriter = null;
		try {
			File commodityFile = new File("commodity.sql");

			commodityWriter = new FileWriter(commodityFile);
			commodityWriter.append("TRUNCATE " + SCHEMA + ".tg_commodity;\n");
			for (String sql : commoditySql) {
				commodityWriter.append(sql);
				commodityWriter.append("\n");
			}
			commodityWriter.flush();
			commodityWriter.close();

			File brandFile = new File("brand.sql");

			brandWriter = new FileWriter(brandFile);
			brandWriter.append("TRUNCATE " + SCHEMA + ".tg_brand;\n");
			for (String sql : brandSql) {
				brandWriter.append(sql);
				brandWriter.append("\n");
			}
			brandWriter.flush();
			brandWriter.close();

			File categoryFile = new File("category.sql");

			categoryWriter = new FileWriter(categoryFile);
			categoryWriter.append("TRUNCATE " + SCHEMA + ".tg_classification_first;\n");
			for (String sql : categorySql) {
				categoryWriter.append(sql);
				categoryWriter.append("\n");
			}

			categoryWriter.flush();
			categoryWriter.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		} finally {
			if (commodityWriter != null) {
				try {
					commodityWriter.close();
				} catch (IOException ex) {
					ex.printStackTrace();
				}
			}
			if (brandWriter != null) {
				try {
					brandWriter.close();
				} catch (IOException ex) {
					ex.printStackTrace();
				}
			}
			if (categoryWriter != null) {
				try {
					categoryWriter.close();
				} catch (IOException ex) {
					ex.printStackTrace();
				}
			}
		}
	}

	private List<OriginalProduct> read(String fileName) {
		List<OriginalProduct> products = new ArrayList<>();

		FileInputStream io = null;
		Workbook workbook = null;

		try {
			io = new FileInputStream(fileName);
			workbook = WorkbookFactory.create(io);
			Sheet sheet = workbook.getSheetAt(0);
			int rowCount = sheet.getPhysicalNumberOfRows();

			for (int i = 1; i < rowCount; i++) {
				Row row = sheet.getRow(i);
				OriginalProduct product = new OriginalProduct();

				String id = row.getCell(0).getStringCellValue();
				product.setId(id);

				String name = row.getCell(1).getStringCellValue();
				product.setName(name);

				String url = row.getCell(2).getStringCellValue();
				product.setUrl(url);

				double price = Double.valueOf(row.getCell(3).getStringCellValue());
				product.setPrice(price);

				String imgUrl = row.getCell(4).getStringCellValue();
				product.setImgUrl(imgUrl);

				double originalPrice = Double.valueOf(row.getCell(5).getStringCellValue());
				product.setOriginalPrice(originalPrice);

				double discount = Double.valueOf(row.getCell(6).getStringCellValue());
				product.setDiscount(discount);

				String brand = row.getCell(7).getStringCellValue();
				product.setBrand(brand);

				String shopName = row.getCell(8).getStringCellValue();
				product.setShopName(shopName);

				boolean isDeliveryFree = "是".equals(row.getCell(9).getStringCellValue());
				product.setDeliveryFree(isDeliveryFree);

				String category = row.getCell(10).getStringCellValue();
				product.setCategory(category);

				int historySales = Double.valueOf(row.getCell(11).getNumericCellValue()).intValue();
				product.setHistorySales(historySales);

				String ownerId = row.getCell(12).getStringCellValue();
				product.setOwnerId(ownerId);

				double commissionRate = Double.valueOf(row.getCell(13).getStringCellValue());
				product.setCommissionRate(commissionRate);

				double commission = Double.valueOf(row.getCell(14).getStringCellValue());
				product.setCommission(commission);

				Date startDate = FORMATTER.parse(row.getCell(15).getStringCellValue());
				product.setStartDate(startDate);

				Date endDate = FORMATTER.parse(row.getCell(16).getStringCellValue());
				product.setEndDate(endDate);

				boolean isAssociateSale = "支持".equals(row.getCell(17).getStringCellValue());
				product.setAssociateSale(isAssociateSale);

				products.add(product);
			}

			workbook.close();
		} catch (IOException | InvalidFormatException | ParseException ex) {
			ex.printStackTrace();
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException ex) {
					ex.printStackTrace();
				}
			}
			if (io != null) {
				try {
					io.close();
				} catch (IOException ex) {
					ex.printStackTrace();
				}
			}
		}

		return products;
	}

	private String escape(String str) {
		return str.replaceAll("\\\\", "\\\\\\\\").replaceAll("'", "\\\\'").replaceAll("\"", "\\\\\"");
	}

	private String addImageHeadUrl(String url) {
		if (!url.startsWith("http://")) {
			return IMG_HEAD + url;
		}
		return url;
	}

	private String cutFileName(String fullName) {
		return fullName.substring(0, fullName.lastIndexOf("."));
	}

	private String cutBrandId(String idName) {
		return idName.substring(0, idName.indexOf("."));
	}

	private String cutBrandName(String idName) {
		return idName.substring(idName.indexOf(".") + 1);
	}

	public static void main(String[] args) {
		ExcelDataImport app = new ExcelDataImport();
		app.run("import");
	}
}
